package top.shiyiri.blob;

import org.junit.Test;
import top.shiyiri.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;

/**
 * @author Aunean
 * @date 2022/1/19 18:28
 */
/*
使用 PreparedStatement 实现批量数据的操作
update、delete 本身就具有批量操作的效果
此时的批量操作，主要指的是批量插入，使用 PreparedStatement 如何实现更高效的批量插入

例题：向 goods 表中插入两万条数据


 */
public class InsertTest {

    //方式一：使用 Statement
    @Test
    public void test1() {
        long startTime = System.currentTimeMillis();
        Connection conn = null;
        Statement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            for (int i = 0; i < 20000; i++) {
                String sql = "insert into goods(name) values('name_"+i+"')";
                ps = conn.createStatement();
                ps.executeUpdate(sql);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
        //54706
        System.out.println(System.currentTimeMillis() - startTime);
    }

    //方式二：使用 PreparedStatement
    @Test
    public void test2() {
        long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into goods(name) values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < 20000; i++) {
                ps.setString(1, "name_"+i);
                ps.executeUpdate();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
        //55488
        System.out.println(System.currentTimeMillis() - startTime);
    }

    //批量插入方式三：addBatch()、executeBatch()、clearBatch()
    @Test
    public void test3() {
        long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            String sql = "insert into goods(name) values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 2000000; i++) {
                ps.setString(1, "name_"+i);
                //1.攒数据
                ps.addBatch();

                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
        //两万条：55488 -> 1706
        //两百万条：39052
        System.out.println(System.currentTimeMillis() - startTime);
    }

    //批量插入方式四，设置连接不允许自动提交数据
    @Test
    public void test4() {
        long startTime = System.currentTimeMillis();
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtils.getConnection();
            //设置不允许自动提交数据
            conn.setAutoCommit(false);

            String sql = "insert into goods(name) values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 2000000; i++) {
                ps.setString(1, "name_"+i);
                //1.攒数据
                ps.addBatch();

                if (i % 500 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            //提交数据
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
        //两百万条：39052 -> 20014
        System.out.println(System.currentTimeMillis() - startTime);
    }
}
